from typing import Optional
from sqlmodel import and_, select, func, text
from fastapi import status
from .base import BaseService, QueryService
from app import models
from datetime import datetime


class MatchPredictService(BaseService[models.MatchPredictEurope168]):
    
    def __init__(self, db, current_user):
        super().__init__(db, current_user, models.MatchPredictEurope168)
    
    async def get_match_predict_europe_168(
        self, 
        payload: models.MatchPredictEurope168Condition
    ) -> models.MatchPredictEurope168ResultResponse:
        """
        根据条件查询欧洲赔率预测数据
        使用优雅的循环方式构建查询条件
        """
        # 定义字段映射配置
        field_mappings = {
            # 文本字段
            "event_name": {
                "field": "event_name",
                "type": "text",
                "exact_match": True
            },
            "order_num": {
                "field": "order_num",
                "type": "text",
                "exact_match": True
            },
            "homesxname": {
                "field": "homesxname",
                "type": "text",
                "exact_match": False
            },
            "awaysxname": {
                "field": "awaysxname",
                "type": "text",
                "exact_match": False
            },
            
            # 比赛日期字段 
            "matchtime": {
                "field": "matchtime",
                "type": "date",
                "start_field": "s_time",
                "end_field": "e_time"
            },
            "ownerdate": {
                "field": "ownerdate",
                "type": "date",
                "start_field": "ownerdate_s",
                "end_field": "ownerdate_e"
            },
            
            # 初始赔率范围字段
            "home_initial": {
                "field": "home_initial",
                "type": "range",
                "min_field": "home_initial_min",
                "max_field": "home_initial_max"
            },
            "draw_initial": {
                "field": "draw_initial",
                "type": "range",
                "min_field": "draw_initial_min",
                "max_field": "draw_initial_max"
            },
            "away_initial": {
                "field": "away_initial",
                "type": "range",
                "min_field": "away_initial_min",
                "max_field": "away_initial_max"
            },
            
            # 最终赔率范围字段
            "home_final": {
                "field": "home_final",
                "type": "range",
                "min_field": "home_final_min",
                "max_field": "home_final_max"
            },
            "draw_final": {
                "field": "draw_final",
                "type": "range",
                "min_field": "draw_final_min",
                "max_field": "draw_final_max"
            },
            "away_final": {
                "field": "away_final",
                "type": "range",
                "min_field": "away_final_min",
                "max_field": "away_final_max"
            },
            
            # 比分范围字段
            "homescore": {
                "field": "homescore",
                "type": "range",
                "min_field": "homescore_min",
                "max_field": "homescore_max"
            },
            "awayscore": {
                "field": "awayscore",
                "type": "range",
                "min_field": "awayscore_min",
                "max_field": "awayscore_max"
            },
            
            # 胜负概率范围字段
            "prob_home_win": {
                "field": "prob_home_win",
                "type": "range",
                "min_field": "prob_home_win_min",
                "max_field": "prob_home_win_max"
            },
            "prob_draw": {
                "field": "prob_draw",
                "type": "range",
                "min_field": "prob_draw_min",
                "max_field": "prob_draw_max"
            },
            "prob_away_win": {
                "field": "prob_away_win",
                "type": "range",
                "min_field": "prob_away_win_min",
                "max_field": "prob_away_win_max"
            }
        }
        
        # 统一异常处理
        try:
            # 构建查询条件
            conditions = self.build_conditions_from_payload(payload, field_mappings)
            
            # 执行分页查询
            result_data = await self.get_paginated_results(
                conditions=conditions,
                order_by_field=models.MatchPredictEurope168.order_num,
                descending=False,
                pagination=payload
            )
            
           
            # 如果没有数据，返回404响应
            if result_data["total"] == 0:
                return models.MatchPredictEurope168ResultResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的预测数据",
                    data=None,
                    pagination=result_data["pagination"]
                )
            
            # 使用公共方法创建分页响应
            return self.create_paginated_response(
                result_data=result_data,
                response_class=models.MatchPredictEurope168ResultResponse,
                message="检索成功",
                code=0
            )
        except Exception as e:
            # 其他系统错误
            return models.MatchPredictEurope168ResultResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None,
                pagination=models.PaginationInfo(
                    current_page=payload.current_page,
                    page_size=payload.page_size,
                    total=0,
                    total_pages=0
                )
            )


class HistoricalSimilarService(QueryService):
    """历史同概统计服务"""
    
    async def get_historical_similar_stats(
        self, 
        payload: models.HistoricalSimilarCondition
    ) -> models.HistoricalSimilarResponse:
        """
        获取历史同概统计信息
        根据概率范围查询匹配的比赛统计信息
        """
        try:
            # 构建基础SQL查询
            sql_query = text("""
                SELECT 
                    COUNT(*) AS total_matches,
                    SUM(CASE WHEN mia.homescore > mia.awayscore THEN 1 ELSE 0 END) AS home_wins,
                    SUM(CASE WHEN mia.homescore = mia.awayscore THEN 1 ELSE 0 END) AS draws,
                    SUM(CASE WHEN mia.homescore < mia.awayscore THEN 1 ELSE 0 END) AS away_wins,
                    ROUND(SUM(CASE WHEN mia.homescore > mia.awayscore THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS home_win_rate,
                    ROUND(SUM(CASE WHEN mia.homescore = mia.awayscore THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS draw_rate,
                    ROUND(SUM(CASE WHEN mia.homescore < mia.awayscore THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS away_win_rate
                FROM match_info_500_all mia
                JOIN public.match_predict_europe_prob op ON mia.fid = op.fid
                WHERE mia.homescore IS NOT NULL
                AND mia.event_name IN ('英超', '意甲', '德甲', '法甲', '西甲')
                AND DATE(mia.matchtime) BETWEEN '2016-01-01' AND '2025-08-30'
                AND mia.status = '4'
            """)
            
            # 添加概率范围条件 - 使用默认值-999到999如果用户没有提供
            conditions = []
            params = {}
            
            # 主胜概率范围
            prob_home_win_min = payload.prob_home_win_min if payload.prob_home_win_min is not None else -999
            prob_home_win_max = payload.prob_home_win_max if payload.prob_home_win_max is not None else 999
            conditions.append("op.prob_home_win BETWEEN :prob_home_win_min AND :prob_home_win_max")
            params["prob_home_win_min"] = prob_home_win_min
            params["prob_home_win_max"] = prob_home_win_max
            
            # 平局概率范围
            prob_draw_min = payload.prob_draw_min if payload.prob_draw_min is not None else -999
            prob_draw_max = payload.prob_draw_max if payload.prob_draw_max is not None else 999
            conditions.append("op.prob_draw BETWEEN :prob_draw_min AND :prob_draw_max")
            params["prob_draw_min"] = prob_draw_min
            params["prob_draw_max"] = prob_draw_max
            
            # 客胜概率范围
            prob_away_win_min = payload.prob_away_win_min if payload.prob_away_win_min is not None else -999
            prob_away_win_max = payload.prob_away_win_max if payload.prob_away_win_max is not None else 999
            conditions.append("op.prob_away_win BETWEEN :prob_away_win_min AND :prob_away_win_max")
            params["prob_away_win_min"] = prob_away_win_min
            params["prob_away_win_max"] = prob_away_win_max
            
            # 如果有条件，添加到查询中
            if conditions:
                sql_query = text(str(sql_query) + " AND " + " AND ".join(conditions))
            print(params)
            print(sql_query.params(**params))
            
            # 执行查询 - 使用正确的参数传递方式
            result = self.db.exec(sql_query.params(**params)).first()
            
            if not result:
                return models.HistoricalSimilarResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的历史数据",
                    data=None
                )
            
            # 构建响应数据
            stats = models.HistoricalSimilarStats(
                total_matches=result[0] or 0,
                home_wins=result[1] or 0,
                draws=result[2] or 0,
                away_wins=result[3] or 0,
                home_win_rate=result[4] or 0.0,
                draw_rate=result[5] or 0.0,
                away_win_rate=result[6] or 0.0
            )
            
            return models.HistoricalSimilarResponse(
                code=0,
                message="检索成功",
                data=stats
            )
            
        except Exception as e:
            return models.HistoricalSimilarResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None
            )


class ColdPlayAnalysisService(QueryService):
    """爆冷分析服务"""
    
    async def get_cold_play_analysis_stats(
        self, 
        payload: models.ColdPlayAnalysisCondition
    ) -> models.ColdPlayAnalysisResponse:
        """
        获取爆冷分析统计信息
        根据条件查询匹配的比赛统计信息，返回爆冷场次和爆冷率
        """
        try:
            # 构建基础SQL查询
            sql_query = text("""
                SELECT 
                    COUNT(*) AS total_matches,
                    SUM(CASE 
                        WHEN (mia.home_initial <= 1.7 ) AND mia.homescore <= mia.awayscore THEN 1  -- 主胜赔率≤1.7但主队没赢
                        WHEN (mia.away_initial <= 1.7 ) AND mia.homescore >= mia.awayscore THEN 1  -- 客胜赔率≤1.7但客队没赢
                        ELSE 0 
                    END) AS error_matches
                FROM t_cold_analysis mia 
                WHERE (mia.homeid = :teamid OR mia.awayid = :teamid)
                    AND DATE(mia.matchtime) < :match_date 
                    AND mia.season_id = :season_id
                    AND (mia.home_initial <= 1.7 OR mia.away_initial <= 1.7 
                    and mia.status='4'  
    )
            """)
            
            # 准备参数
            params = {
                "teamid": payload.teamid if payload.teamid is not None else 0,
                "match_date": payload.match_date if payload.match_date is not None else "2025-10-24",
                "season_id": payload.season_id if payload.season_id is not None else 0
            }
            
            # 执行查询
            result = self.db.exec(sql_query.params(**params)).first()
            
            if not result:
                return models.ColdPlayAnalysisResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的数据",
                    data=None
                )
            
            # 计算错误率
            total_matches = result[0] or 0
            error_matches = result[1] or 0
            error_matches_rate = 0.0
            if total_matches > 0:
                error_matches_rate = error_matches / total_matches
            
            # 构建响应数据
            stats = models.ColdPlayAnalysisStats(
                total_matches=total_matches,
                error_matches=error_matches,
                error_matches_rate=error_matches_rate
            )
            
            return models.ColdPlayAnalysisResponse(
                code=0,
                message="检索成功",
                data=stats
            )
            
        except Exception as e:
            return models.ColdPlayAnalysisResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None
            )


class HistoricalSameOddsService(BaseService[models.HistoricalSameOddsData]):
    """历史同赔查询服务"""
    
    def __init__(self, db, current_user):
        super().__init__(db, current_user, models.HistoricalSameOddsData)
    
    async def get_historical_same_odds(
        self, 
        payload: models.HistoricalSameOddsCondition
    ) -> models.HistoricalSameOddsResponse:
        """
        根据条件查询历史同赔数据
        从 t_match_europe_168 表中查询符合条件的记录
        使用优雅的循环方式构建查询条件，支持范围查询
        """
        # 定义字段映射配置
        field_mappings = {
            # 文本字段
            "status": {
                "field": "status",
                "type": "text",
                "exact_match": True
            },
            "event_name": {
                "field": "event_name",
                "type": "text",
                "exact_match": True
            },
            "order_num": {
                "field": "order_num",
                "type": "text",
                "exact_match": True
            },
            "homesxname": {
                "field": "homesxname",
                "type": "text",
                "exact_match": False
            },
            "awaysxname": {
                "field": "awaysxname",
                "type": "text",
                "exact_match": False
            },
            
            # 日期字段 
            "ownerdate": {
                "field": "ownerdate",
                "type": "date",
                "start_field": "ownerdate_s",
                "end_field": "ownerdate_e"
            },
            "matchtime": {
                "field": "matchtime",
                "type": "date",
                "start_field": "matchtime_s",
                "end_field": "matchtime_e"
            },
            
            # 比分范围字段
            "homescore": {
                "field": "homescore",
                "type": "range",
                "min_field": "homescore_min",
                "max_field": "homescore_max"
            },
            "awayscore": {
                "field": "awayscore",
                "type": "range",
                "min_field": "awayscore_min",
                "max_field": "awayscore_max"
            },
            
            # 平均积分范围字段
            "home_avg_points": {
                "field": "home_avg_points",
                "type": "range",
                "min_field": "home_avg_points_min",
                "max_field": "home_avg_points_max"
            },
            "away_avg_points": {
                "field": "away_avg_points",
                "type": "range",
                "min_field": "away_avg_points_min",
                "max_field": "away_avg_points_max"
            },
            
            # 主队赔率范围字段
            "home_initial": {
                "field": "home_initial",
                "type": "range",
                "min_field": "home_initial_min",
                "max_field": "home_initial_max"
            },
            "draw_initial": {
                "field": "draw_initial",
                "type": "range",
                "min_field": "draw_initial_min",
                "max_field": "draw_initial_max"
            },
            "away_initial": {
                "field": "away_initial",
                "type": "range",
                "min_field": "away_initial_min",
                "max_field": "away_initial_max"
            },
            "home_final": {
                "field": "home_final",
                "type": "range",
                "min_field": "home_final_min",
                "max_field": "home_final_max"
            },
            "draw_final": {
                "field": "draw_final",
                "type": "range",
                "min_field": "draw_final_min",
                "max_field": "draw_final_max"
            },
            
            # 平局赔率范围字段
            "away_final": {
                "field": "away_final",
                "type": "range",
                "min_field": "away_final_min",
                "max_field": "away_final_max"
            },
            "prob_home_initial": {
                "field": "prob_home_initial",
                "type": "range",
                "min_field": "prob_home_initial_min",
                "max_field": "prob_home_initial_max"
            },
            "prob_draw_initial": {
                "field": "prob_draw_initial",
                "type": "range",
                "min_field": "prob_draw_initial_min",
                "max_field": "prob_draw_initial_max"
            },
            "prob_away_initial": {
                "field": "prob_away_initial",
                "type": "range",
                "min_field": "prob_away_initial_min",
                "max_field": "prob_away_initial_max"
            },
            "prob_home_final": {
                "field": "prob_home_final",
                "type": "range",
                "min_field": "prob_home_final_min",
                "max_field": "prob_home_final_max"
            },
            
            # 客队赔率范围字段
            "prob_draw_final": {
                "field": "prob_draw_final",
                "type": "range",
                "min_field": "prob_draw_final_min",
                "max_field": "prob_draw_final_max"
            },
            "prob_away_final": {
                "field": "prob_away_final",
                "type": "range",
                "min_field": "prob_away_final_min",
                "max_field": "prob_away_final_max"
            },
            "return_rate_initial": {
                "field": "return_rate_initial",
                "type": "range",
                "min_field": "return_rate_initial_min",
                "max_field": "return_rate_initial_max"
            },
            "return_rate_final": {
                "field": "return_rate_final",
                "type": "range",
                "min_field": "return_rate_final_min",
                "max_field": "return_rate_final_max"
            }
           
            
            
            
        }
        
        # 统一异常处理
        try:
            # 构建查询条件
            conditions = self.build_conditions_from_payload(payload, field_mappings)
            
            # 执行分页查询
            
            result_data = await self.get_paginated_results(
                conditions=conditions,
                order_by_field=models.HistoricalSameOddsData.order_num,
                descending=False,
                pagination=payload,
                
            )
            
            # 如果没有数据，返回404响应
            if result_data["total"] == 0:
                return models.HistoricalSameOddsResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的历史同赔数据",
                    data=None,
                    pagination=result_data["pagination"]
                )
            
            # 使用公共方法创建分页响应
            return self.create_paginated_response(
                result_data=result_data,
                response_class=models.HistoricalSameOddsResponse,
                message="检索成功",
                code=0
            )
        except Exception as e:
            # 其他系统错误
            return models.HistoricalSameOddsResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None,
                pagination=models.PaginationInfo(
                    current_page=payload.current_page,
                    page_size=payload.page_size,
                    total=0,
                    total_pages=0
                )
            )


class SameOdds168AnalysisService(QueryService):
    """同赔168分析服务"""
    
    async def get_same_odds_168_analysis(
        self, 
        payload: models.SameOdds168AnalysisCondition
    ) -> models.SameOdds168AnalysisResponse:
        """
        获取同赔168分析统计信息
        根据赔率范围查询 t_match_europe_168 表中符合条件的比赛统计信息
        返回胜平负统计和最常见比分
        """
        try:
            # 构建完整的SQL查询，包含比分统计
            sql_query = text("""
                WITH score_counts AS (
                    SELECT 
                        CONCAT(eo168.homescore, '-', eo168.awayscore) AS score_combination,
                        COUNT(*) AS score_count,
                        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
                    FROM t_match_europe_168 eo168 
                    
                    WHERE 
                         eo168.status = '4'
                        AND date(eo168.matchtime) < :matchtime_max
                        AND eo168.home_odds_0 BETWEEN :home_odds_0_min AND :home_odds_0_max
                        AND eo168.home_odds_24 BETWEEN :home_odds_24_min AND :home_odds_24_max
                        AND eo168.home_odds_48 BETWEEN :home_odds_48_min AND :home_odds_48_max
                        AND eo168.home_odds_72 BETWEEN :home_odds_72_min AND :home_odds_72_max
                        AND eo168.home_odds_167 BETWEEN :home_odds_167_min AND :home_odds_167_max
                        
                        AND eo168.draw_odds_0 BETWEEN :draw_odds_0_min AND :draw_odds_0_max
                        AND eo168.draw_odds_24 BETWEEN :draw_odds_24_min AND :draw_odds_24_max
                        AND eo168.draw_odds_48 BETWEEN :draw_odds_48_min AND :draw_odds_48_max
                        AND eo168.draw_odds_72 BETWEEN :draw_odds_72_min AND :draw_odds_72_max
                        AND eo168.draw_odds_167 BETWEEN :draw_odds_167_min AND :draw_odds_167_max
                        
                        
                        AND eo168.away_odds_0 BETWEEN :away_odds_0_min AND :away_odds_0_max
                        AND eo168.away_odds_24 BETWEEN :away_odds_24_min AND :away_odds_24_max
                        AND eo168.away_odds_48 BETWEEN :away_odds_48_min AND :away_odds_48_max
                        AND eo168.away_odds_72 BETWEEN :away_odds_72_min AND :away_odds_72_max
                        AND eo168.away_odds_167 BETWEEN :away_odds_167_min AND :away_odds_167_max
                """)
            
            # 添加赛事名称条件（如果提供）
            if payload.event_name:
                sql_query = text(str(sql_query) + " AND eo168.event_name = :event_name")
            
            sql_query = text(str(sql_query) + """
                    GROUP BY eo168.homescore, eo168.awayscore
                ),
                top_scores AS (
                    SELECT 
                        MAX(CASE WHEN rn = 1 THEN score_combination END) AS most_common_score,
                        MAX(CASE WHEN rn = 2 THEN score_combination END) AS second_most_common_score,
                        MAX(CASE WHEN rn = 3 THEN score_combination END) AS third_most_common_score
                    FROM score_counts
                    WHERE rn <= 3
                )
                SELECT 
                    main.total_matches,
                    main.home_wins,
                    main.draws,
                    main.away_wins,
                    main.home_win_rate,
                    main.draw_rate,
                    main.away_win_rate,
                    ts.most_common_score,
                    ts.second_most_common_score,
                    ts.third_most_common_score
                FROM (
                    SELECT 
                        COUNT(*) AS total_matches,
                        SUM(CASE WHEN eo168.homescore > eo168.awayscore THEN 1 ELSE 0 END) AS home_wins,
                        SUM(CASE WHEN eo168.homescore = eo168.awayscore THEN 1 ELSE 0 END) AS draws,
                        SUM(CASE WHEN eo168.homescore < eo168.awayscore THEN 1 ELSE 0 END) AS away_wins,
                        ROUND(SUM(CASE WHEN eo168.homescore > eo168.awayscore THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS home_win_rate,
                        ROUND(SUM(CASE WHEN eo168.homescore = eo168.awayscore THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS draw_rate,
                        ROUND(SUM(CASE WHEN eo168.homescore < eo168.awayscore THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS away_win_rate
                    FROM t_match_europe_168 eo168 
                    
                    WHERE 
                         eo168.status = '4'
                        AND date(eo168.matchtime) < :matchtime_max
                        AND eo168.home_odds_0 BETWEEN :home_odds_0_min AND :home_odds_0_max
                        AND eo168.home_odds_24 BETWEEN :home_odds_24_min AND :home_odds_24_max
                        AND eo168.home_odds_48 BETWEEN :home_odds_48_min AND :home_odds_48_max
                        AND eo168.home_odds_72 BETWEEN :home_odds_72_min AND :home_odds_72_max
                        AND eo168.home_odds_167 BETWEEN :home_odds_167_min AND :home_odds_167_max
                        
                        AND eo168.draw_odds_0 BETWEEN :draw_odds_0_min AND :draw_odds_0_max
                        AND eo168.draw_odds_24 BETWEEN :draw_odds_24_min AND :draw_odds_24_max
                        AND eo168.draw_odds_48 BETWEEN :draw_odds_48_min AND :draw_odds_48_max
                        AND eo168.draw_odds_72 BETWEEN :draw_odds_72_min AND :draw_odds_72_max
                        AND eo168.draw_odds_167 BETWEEN :draw_odds_167_min AND :draw_odds_167_max
                        
                        
                        AND eo168.away_odds_0 BETWEEN :away_odds_0_min AND :away_odds_0_max
                        AND eo168.away_odds_24 BETWEEN :away_odds_24_min AND :away_odds_24_max
                        AND eo168.away_odds_48 BETWEEN :away_odds_48_min AND :away_odds_48_max
                        AND eo168.away_odds_72 BETWEEN :away_odds_72_min AND :away_odds_72_max
                        AND eo168.away_odds_167 BETWEEN :away_odds_167_min AND :away_odds_167_max
                        
                """)
            
            # 添加赛事名称条件（如果提供）
            if payload.event_name:
                sql_query = text(str(sql_query) + " AND eo168.event_name = :event_name")
            
            sql_query = text(str(sql_query) + """
                ) main
                CROSS JOIN top_scores ts
            """)
            

            
            # 准备参数
            params = {
                "matchtime_max": payload.matchtime_max if payload.matchtime_max else "2025-10-30",
                "home_odds_0_min": payload.home_odds_0_min if payload.home_odds_0_min is not None else 0,
                "home_odds_0_max": payload.home_odds_0_max if payload.home_odds_0_max is not None else 999,
                "home_odds_24_min": payload.home_odds_24_min if payload.home_odds_24_min is not None else 0,
                "home_odds_24_max": payload.home_odds_24_max if payload.home_odds_24_max is not None else 999,
                "home_odds_48_min": payload.home_odds_48_min if payload.home_odds_48_min is not None else 0,
                "home_odds_48_max": payload.home_odds_48_max if payload.home_odds_48_max is not None else 999,
                "home_odds_72_min": payload.home_odds_72_min if payload.home_odds_72_min is not None else 0,
                "home_odds_72_max": payload.home_odds_72_max if payload.home_odds_72_max is not None else 999,
                "home_odds_167_min": payload.home_odds_167_min if payload.home_odds_167_min is not None else 0,
                "home_odds_167_max": payload.home_odds_167_max if payload.home_odds_167_max is not None else 999,
                "draw_odds_0_min": payload.draw_odds_0_min if payload.draw_odds_0_min is not None else 0,
                "draw_odds_0_max": payload.draw_odds_0_max if payload.draw_odds_0_max is not None else 999,
                "draw_odds_24_min": payload.draw_odds_24_min if payload.draw_odds_24_min is not None else 0,
                "draw_odds_24_max": payload.draw_odds_24_max if payload.draw_odds_24_max is not None else 999,
                "draw_odds_48_min": payload.draw_odds_48_min if payload.draw_odds_48_min is not None else 0,
                "draw_odds_48_max": payload.draw_odds_48_max if payload.draw_odds_48_max is not None else 999,
                "draw_odds_72_min": payload.draw_odds_72_min if payload.draw_odds_72_min is not None else 0,
                "draw_odds_72_max": payload.draw_odds_72_max if payload.draw_odds_72_max is not None else 999,
                "draw_odds_167_min": payload.draw_odds_167_min if payload.draw_odds_167_min is not None else 0,
                "draw_odds_167_max": payload.draw_odds_167_max if payload.draw_odds_167_max is not None else 999,
                "away_odds_0_min": payload.away_odds_0_min if payload.away_odds_0_min is not None else 0,
                "away_odds_0_max": payload.away_odds_0_max if payload.away_odds_0_max is not None else 999,
                "away_odds_24_min": payload.away_odds_24_min if payload.away_odds_24_min is not None else 0,
                "away_odds_24_max": payload.away_odds_24_max if payload.away_odds_24_max is not None else 999,
                "away_odds_48_min": payload.away_odds_48_min if payload.away_odds_48_min is not None else 0,
                "away_odds_48_max": payload.away_odds_48_max if payload.away_odds_48_max is not None else 999,
                "away_odds_72_min": payload.away_odds_72_min if payload.away_odds_72_min is not None else 0,
                "away_odds_72_max": payload.away_odds_72_max if payload.away_odds_72_max is not None else 999,
                "away_odds_167_min": payload.away_odds_167_min if payload.away_odds_167_min is not None else 0,
                "away_odds_167_max": payload.away_odds_167_max if payload.away_odds_167_max is not None else 999,
                
            }
            
            # 如果提供了赛事名称，添加到参数中
            if payload.event_name:
                params["event_name"] = payload.event_name
            
            # 执行查询
            result = self.db.exec(sql_query.params(**params)).first()
            
            if not result:
                return models.SameOdds168AnalysisResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的同赔分析数据",
                    data=None
                )
            
            # 构建响应数据
            stats = models.SameOdds168AnalysisStats(
                total_matches=result[0] or 0,
                home_wins=result[1] or 0,
                draws=result[2] or 0,
                away_wins=result[3] or 0,
                home_win_rate=result[4] or 0.0,
                draw_rate=result[5] or 0.0,
                away_win_rate=result[6] or 0.0,
                most_common_score=result[7],
                second_most_common_score=result[8],
                third_most_common_score=result[9]
            )
            
            return models.SameOdds168AnalysisResponse(
                code=0,
                message="检索成功",
                data=stats
            )
            
        except Exception as e:
            return models.SameOdds168AnalysisResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None
            )


class TeamStrengthAnalysisService(QueryService):
    """队伍实力分析服务"""
    
    async def get_team_strength_analysis(
        self, 
        payload: models.TeamStrengthAnalysisCondition
    ) -> models.TeamStrengthAnalysisResponse:
        """
        获取队伍实力分析信息
        根据队伍ID查询该队伍的进攻、防守和综合实力指数
        """
        try:
            # 构建SQL查询，使用用户提供的SQL
            sql_query = text("""                
                select teamgbname                
                ,avg(offensive_score_norm_index) as offensive_score_norm_index
                ,avg(defensive_score_norm_index) as defensive_score_norm_index
                ,avg(offensive_score_norm_index+defensive_score_norm_index) as overall_score_norm_index
                ,coalesce(cc.conversion_factor ,0.5) as conversion_factor
                from t_player_classification_matrix m
                left join team_info ti on m.teamid =ti.teamid 
                left join public.country_index cc on ti.countryname =cc.country_cn
                left join public.suspension_list sl on m.teamid =sl.teamid and m.playerid =sl.playerid and sl.fid=:fid
                where m.teamid =:teamid and sl.playerid is null
                group by teamgbname,cc.conversion_factor
            """)
            
            # 准备参数
            params = {
                "teamid": payload.teamid,
                 "fid": payload.fid
            }
            
            # 执行查询
            result = self.db.exec(sql_query.params(**params)).first()
            
            if not result:
                return models.TeamStrengthAnalysisResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的队伍数据",
                    data=None
                )
            
            # 构建响应数据
            stats = models.TeamStrengthAnalysisData(
                teamid=payload.teamid,
                teamgbname=result[0],                
                offensive_score_norm_index=result[1],
                defensive_score_norm_index=result[2],
                overall_score_norm_index=result[3],
                conversion_factor=result[4]
            )
            
            return models.TeamStrengthAnalysisResponse(
                code=0,
                message="检索成功",
                data=stats
            )
            
        except Exception as e:
            return models.TeamStrengthAnalysisResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None
            )


class PlayerStrengthAnalysisService(QueryService):
    """球员实力分析服务"""

    async def get_player_strength_analysis(
        self,
        payload: models.PlayerStrengthAnalysisCondition
    ) -> models.PlayerStrengthAnalysisResponse:
        """
        获取球员实力分析信息
        根据FID和队伍ID查询该队伍球员的进攻、防守和综合能力指数
        """
        try:
            # 构建SQL查询，使用用户提供的SQL
            sql_query = text("""
                select teamgbname, m.playerid, playername, player_category, sl.suspension_reason
                ,avg(total_goals) as avg_goals
                ,avg(offensive_score_norm_index) as offensive_score_norm_index
                ,avg(defensive_score_norm_index) as defensive_score_norm_index
                ,avg(offensive_score_norm_index+defensive_score_norm_index) as overall_score_norm_index
                ,coalesce(cc.conversion_factor ,0.5) as conversion_factor
                from t_player_classification_matrix m
                left join team_info ti on m.teamid =ti.teamid
                left join public.country_index cc on ti.countryname =cc.country_cn
                left join public.suspension_list sl on m.teamid =sl.teamid and m.playerid =sl.playerid and sl.fid=:fid
                where m.teamid =:teamid
                group by teamgbname, m.playerid, playername, player_category, sl.suspension_reason, cc.conversion_factor
                order by overall_score_norm_index desc, player_category
            """)

            # 准备参数
            params = {
                "fid": payload.fid,
                "teamid": payload.teamid
            }

            # 执行查询
            results = self.db.exec(sql_query.params(**params)).all()

            if not results:
                return models.PlayerStrengthAnalysisResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的球员数据",
                    data=[]
                )

            # 构建响应数据
            player_data_list = []
            for result in results:
                player_data = models.PlayerStrengthAnalysisData(
                    teamgbname=result[0],
                    playerid=result[1],
                    playername=result[2],
                    player_category=result[3],
                    suspension_reason=result[4],
                    avg_goals=result[5],
                    offensive_score_norm_index=result[6],
                    defensive_score_norm_index=result[7],
                    overall_score_norm_index=result[8],
                    conversion_factor=result[9]
                )
                player_data_list.append(player_data)

            return models.PlayerStrengthAnalysisResponse(
                code=0,
                message="检索成功",
                data=player_data_list
            )

        except Exception as e:
            return models.PlayerStrengthAnalysisResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None
            )
